using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class OrderDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procOrder_add]";
        private string _strSPUpdateName = "dbo.[procOrder_update]";
        private string _strSPDeleteName = "dbo.[procOrder_delete]";
        private string _strSPGetName = "dbo.[procOrder_get]";
        private string _strSPGetName_ByUserID = "dbo.[procOrder_get_ByUserID]";
        private string _strSPGetName_ALLDetail = "dbo.[procOrder_get_ALLDetail]";
        private string _strSPGetAllName = "dbo.[procOrder_getall]";
        private string _strSPGetPages = "dbo.[procOrder_getpaged]";
        private string _strSPIsExist = "dbo.[procOrder_isexist]";
        private string _strTableName = "[Order]";
        private string _strSPGetTransferOutName = "dbo.[procOrder_gettransferout]";
        #endregion Local Variable

        #region Method
        public OrderInfo Get(
        Int32 ID,
        ref string sErr)
        {
            OrderInfo objEntr = new OrderInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(OrderInfo.Field.ID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (OrderInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            OrderInfo result = new OrderInfo();
            result.ID = (dt.Rows[i][OrderInfo.Field.ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][OrderInfo.Field.ID.ToString()]));
            result.UserID = (dt.Rows[i][OrderInfo.Field.UserID.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][OrderInfo.Field.UserID.ToString()]));
            result.Bill_Name = (dt.Rows[i][OrderInfo.Field.Bill_Name.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Bill_Name.ToString()]));
            result.Bill_Address = (dt.Rows[i][OrderInfo.Field.Bill_Address.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Bill_Address.ToString()]));
            result.Bill_Phone = (dt.Rows[i][OrderInfo.Field.Bill_Phone.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Bill_Phone.ToString()]));
            result.Ship_Name = (dt.Rows[i][OrderInfo.Field.Ship_Name.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Ship_Name.ToString()]));
            result.Ship_Address = (dt.Rows[i][OrderInfo.Field.Ship_Address.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Ship_Address.ToString()]));
            result.Ship_Phone = (dt.Rows[i][OrderInfo.Field.Ship_Phone.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Ship_Phone.ToString()]));
            result.IDStatus = (dt.Rows[i][OrderInfo.Field.IDStatus.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][OrderInfo.Field.IDStatus.ToString()]));
            result.DateTime = (dt.Rows[i][OrderInfo.Field.DateTime.ToString()] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(dt.Rows[i][OrderInfo.Field.DateTime.ToString()]));
            result.OrderProducts = (dt.Rows[i][OrderInfo.Field.OrderProducts.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.OrderProducts.ToString()]));
            result.IDPayment = (dt.Rows[i][OrderInfo.Field.IDPayment.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][OrderInfo.Field.IDPayment.ToString()]));
            result.Bill_City = (dt.Rows[i][OrderInfo.Field.Bill_City.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Bill_City.ToString()]));
            result.Bill_Country = (dt.Rows[i][OrderInfo.Field.Bill_Country.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Bill_Country.ToString()]));
            result.Ship_City = (dt.Rows[i][OrderInfo.Field.Ship_City.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Ship_City.ToString()]));
            result.Ship_Country = (dt.Rows[i][OrderInfo.Field.Ship_Country.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Ship_Country.ToString()]));
            result.Notes = (dt.Rows[i][OrderInfo.Field.Notes.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][OrderInfo.Field.Notes.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetByPos(
        int pos, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            AddParameter("INX", pos);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public int GetCountRecord(
        ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(OrderInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(OrderInfo.Field.UserID.ToString(), objEntr.UserID);
            AddParameter(OrderInfo.Field.Bill_Name.ToString(), objEntr.Bill_Name);
            AddParameter(OrderInfo.Field.Bill_Address.ToString(), objEntr.Bill_Address);
            AddParameter(OrderInfo.Field.Bill_Phone.ToString(), objEntr.Bill_Phone);
            AddParameter(OrderInfo.Field.Ship_Name.ToString(), objEntr.Ship_Name);
            AddParameter(OrderInfo.Field.Ship_Address.ToString(), objEntr.Ship_Address);
            AddParameter(OrderInfo.Field.Ship_Phone.ToString(), objEntr.Ship_Phone);
            AddParameter(OrderInfo.Field.IDStatus.ToString(), objEntr.IDStatus);
            AddParameter(OrderInfo.Field.DateTime.ToString(), objEntr.DateTime);
            AddParameter(OrderInfo.Field.OrderProducts.ToString(), objEntr.OrderProducts);
            AddParameter(OrderInfo.Field.IDPayment.ToString(), objEntr.IDPayment);
            AddParameter(OrderInfo.Field.Bill_City.ToString(), objEntr.Bill_City);
            AddParameter(OrderInfo.Field.Bill_Country.ToString(), objEntr.Bill_Country);
            AddParameter(OrderInfo.Field.Ship_City.ToString(), objEntr.Ship_City);
            AddParameter(OrderInfo.Field.Ship_Country.ToString(), objEntr.Ship_Country);
            AddParameter(OrderInfo.Field.Notes.ToString(), objEntr.Notes);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(OrderInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(OrderInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(OrderInfo.Field.UserID.ToString(), objEntr.UserID);
            AddParameter(OrderInfo.Field.Bill_Name.ToString(), objEntr.Bill_Name);
            AddParameter(OrderInfo.Field.Bill_Address.ToString(), objEntr.Bill_Address);
            AddParameter(OrderInfo.Field.Bill_Phone.ToString(), objEntr.Bill_Phone);
            AddParameter(OrderInfo.Field.Ship_Name.ToString(), objEntr.Ship_Name);
            AddParameter(OrderInfo.Field.Ship_Address.ToString(), objEntr.Ship_Address);
            AddParameter(OrderInfo.Field.Ship_Phone.ToString(), objEntr.Ship_Phone);
            AddParameter(OrderInfo.Field.IDStatus.ToString(), objEntr.IDStatus);
            AddParameter(OrderInfo.Field.DateTime.ToString(), objEntr.DateTime);
            AddParameter(OrderInfo.Field.OrderProducts.ToString(), objEntr.OrderProducts);
            AddParameter(OrderInfo.Field.IDPayment.ToString(), objEntr.IDPayment);
            AddParameter(OrderInfo.Field.Bill_City.ToString(), objEntr.Bill_City);
            AddParameter(OrderInfo.Field.Bill_Country.ToString(), objEntr.Bill_Country);
            AddParameter(OrderInfo.Field.Ship_City.ToString(), objEntr.Ship_City);
            AddParameter(OrderInfo.Field.Ship_Country.ToString(), objEntr.Ship_Country);
            AddParameter(OrderInfo.Field.Notes.ToString(), objEntr.Notes);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int32 ID
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(OrderInfo.Field.ID.ToString(), ID);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(OrderInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        Int32 ID
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(OrderInfo.Field.ID.ToString(), ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(OrderInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method
        public DataTable GetAll_OrderEx(ref string _sErr)
        {
            string query = "SELECT     Order.ID, Order.UserID, Order.Bill_Name, Order.Bill_Email, Order.Bill_Address, Order.Bill_Phone, Order.Ship_Name, Order.Ship_Email, " +
                        "Order.Ship_Address, Order.Ship_Phone, Order.IDStatus, Order.DateTime, Order.OrderProducts, Order.IDPayment, Order.Bill_City, " +
                        "Order.Bill_Country, Order.Ship_City, Order.Ship_Country, Status.NameVN AS StaName, Payment.NameVN AS PayName, " +
                        "SUM(OrderProduct.Price * OrderProduct.Quantity) AS Total " +
                        "FROM         Order INNER JOIN " +
                        "OrderProduct ON Order.OrderProducts = OrderProduct.OrderProducts LEFT OUTER JOIN " +
                        "Status ON Order.IDStatus = Status.ID LEFT OUTER JOIN " +
                        "Payment ON Order.IDPayment = Payment.ID " +
                        "GROUP BY Order.ID, Order.UserID, Order.Bill_Name, Order.Bill_Email, Order.Bill_Phone, Order.Ship_Name, Order.Ship_Email, Order.Ship_Phone, " +
                        "Order.IDStatus, Order.DateTime, Order.OrderProducts, Order.IDPayment, Order.Bill_City, Order.Bill_Country, Order.Ship_City, " +
                        "Order.Ship_Country, Status.NameVN, Payment.NameVN, Order.Bill_Address, Order.Ship_Address";
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                _sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


        public DataTable Get_ByUserID(
       Int32 ID,
       ref string sErr)
        {
            OrderInfo objEntr = new OrderInfo();
            connect();
            if (ID == -1)
                InitSPCommand(_strSPGetName_ALLDetail);
            else
                InitSPCommand(_strSPGetName_ByUserID);
            AddParameter(OrderInfo.Field.UserID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            //if (list.Rows.Count > 0)
            //    objEntr = (OrderInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }


    }
}
